using System.Text;
using SPI.AgentServer.Services.Execution;

namespace SPI.AgentServer.Services.Tools;

/// <summary>
/// Tool to query database schema information (table names, columns, types)
/// </summary>
public class GetDatabaseSchemaTool : IQueryTool
{
    private readonly IDatabaseExecutor _executor;
    private readonly ILogger<GetDatabaseSchemaTool> _logger;

    public string Name => "get_database_schema";
    public string Description => "Query database schema information including table names, column names, and data types. Can filter by table name pattern.";

    public GetDatabaseSchemaTool(IDatabaseExecutor executor, ILogger<GetDatabaseSchemaTool> logger)
    {
        _executor = executor;
        _logger = logger;
    }

    public async Task<ToolResult> ExecuteAsync(Dictionary<string, object> parameters, CancellationToken cancellationToken = default)
    {
        if (!ValidateParameters(parameters, out var errors))
        {
            return new ToolResult
            {
                Success = false,
                Errors = errors
            };
        }

        string? tablePattern = null;
        if (parameters.ContainsKey("table_name_pattern"))
        {
            tablePattern = parameters["table_name_pattern"]?.ToString();
        }

        try
        {
            var sql = BuildSchemaQuery(tablePattern);
            _logger.LogInformation("Executing schema query: {Sql}", sql);

            var result = await _executor.ExecuteQueryAsync(sql, null, cancellationToken);

            return new ToolResult
            {
                Success = true,
                Data = result.Data,
                Message = "数据库表结构查询成功",
                Metadata = new Dictionary<string, object>
                {
                    ["executionTimeMs"] = result.ExecutionTimeMs,
                    ["executedSql"] = sql
                }
            };
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, "Failed to query database schema");
            return new ToolResult
            {
                Success = false,
                Errors = new List<string> { $"查询表结构错误: {ex.Message}" }
            };
        }
    }

    private string BuildSchemaQuery(string? tablePattern)
    {
        var sql = new StringBuilder();
        sql.AppendLine(@"
SELECT
    TABLE_NAME as TableName,
    COLUMN_NAME as ColumnName,
    DATA_TYPE as DataType,
    IS_NULLABLE as IsNullable,
    COLUMN_KEY as ColumnKey,
    COLUMN_COMMENT as ColumnComment
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = DATABASE()");

        if (!string.IsNullOrWhiteSpace(tablePattern))
        {
            sql.AppendLine($"  AND TABLE_NAME LIKE '%{tablePattern}%'");
        }

        sql.AppendLine("ORDER BY TABLE_NAME, ORDINAL_POSITION");
        sql.AppendLine("LIMIT 500");

        return sql.ToString();
    }

    public bool ValidateParameters(Dictionary<string, object> parameters, out List<string> errors)
    {
        errors = new List<string>();
        // 此工具不需要必填参数，所有参数都是可选的
        return true;
    }
}
